package theDaoImpls;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import DbcpPoolPackage.DbcpPool;
import DbcpPoolPackage.needToClose;
import theBeans.position;
import theBeans.student;
import theDaos.IStudentDao;

public class studentDaoImpl implements IStudentDao{
	
	@Override
	public int initialize(String sid, String schoolNum, String credit, String ifConfirm) {
		// TODO Auto-generated method stub
		String sql="INSERT INTO student (sid,schoolNum,credit,ifConfirm) VALUES (?,?,?,?)";
		Connection conn=null;
		try {
			conn=DbcpPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int result=0;
		PreparedStatement ps=DbcpPool.executePreparedStatementReturnPS(conn, sql);
		try
		{
			ps.setString(1, sid);
			ps.setString(2, schoolNum);
			ps.setString(3, credit);
			ps.setString(4, ifConfirm);
			result=ps.executeUpdate();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		needToClose newNeedtoClose=new needToClose();
		newNeedtoClose.setAll(conn, ps, null);
		
		DbcpPool.closeAll(newNeedtoClose);
		return result;
	}
	
	@Override
	public student login(String username, String password) {
		// TODO Auto-generated method stub
		String sql="SELECT * FROM student WHERE username=? AND password=?";
		student studentOption=new student();
		Connection conn = null;
		try {
			conn = DbcpPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		needToClose newNeedtoClose=DbcpPool.executePreparedStatement(conn,sql,username,password);
		ResultSet rs=newNeedtoClose.getRs();
		
		try
		{
			if(rs.next())
			{
				studentOption.setSid(Integer.parseInt(rs.getString("sid")));
				studentOption.setUsername(rs.getString("username"));
				studentOption.setPassword(rs.getString("password"));
				studentOption.setSchoolNum(Integer.parseInt(rs.getString("schoolNum")));
				studentOption.setCredit(Integer.parseInt(rs.getString("credit")));
				studentOption.setIfConfirm(Integer.parseInt(rs.getString("ifConfirm")));
				studentOption.setEmailAddress(rs.getString("emailAddress"));
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		DbcpPool.closeAll(newNeedtoClose);
		return studentOption;
	}

	@Override
	public List<student> findAll() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public student findBySid(String sid) {
		// TODO Auto-generated method stub
		String sql="SELECT * FROM student WHERE sid=?";
		student studentOption=new student();
		Connection conn = null;
		try {
			conn = DbcpPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		needToClose newNeedtoClose=DbcpPool.executePreparedStatement(conn,sql,sid);
		ResultSet rs=newNeedtoClose.getRs();
		
		try
		{
			if(rs.next())
			{
				studentOption.setSid(Integer.parseInt(rs.getString("sid")));
				studentOption.setUsername(rs.getString("username"));
				studentOption.setPassword(rs.getString("password"));
				studentOption.setSchoolNum(Integer.parseInt(rs.getString("schoolNum")));
				studentOption.setCredit(Integer.parseInt(rs.getString("credit")));
				studentOption.setIfConfirm(Integer.parseInt(rs.getString("ifConfirm")));
				studentOption.setEmailAddress(rs.getString("emailAddress"));
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		DbcpPool.closeAll(newNeedtoClose);
		return studentOption;
	}
	
	@Override
	public student findByEmail(String email) {
		// TODO Auto-generated method stub
		String sql="SELECT * FROM student WHERE emailAddress=?";
		student studentOption=new student();
		Connection conn = null;
		try {
			conn = DbcpPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		needToClose newNeedtoClose=DbcpPool.executePreparedStatement(conn,sql,email);
		ResultSet rs=newNeedtoClose.getRs();
		
		try
		{
			if(rs.next())
			{
				studentOption.setSid(Integer.parseInt(rs.getString("sid")));
				studentOption.setUsername(rs.getString("username"));
				studentOption.setPassword(rs.getString("password"));
				studentOption.setSchoolNum(Integer.parseInt(rs.getString("schoolNum")));
				studentOption.setCredit(Integer.parseInt(rs.getString("credit")));
				studentOption.setIfConfirm(Integer.parseInt(rs.getString("ifConfirm")));
				studentOption.setEmailAddress(rs.getString("emailAddress"));
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		DbcpPool.closeAll(newNeedtoClose);
		return studentOption;
	}

	@Override
	public int update(String sid, String username, String password,String emailAddress) {
		// TODO Auto-generated method stub
		String sql="UPDATE student SET username=?,password=?,emailAddress=? WHERE sid=?";
		Connection conn=null;
		try {
			conn=DbcpPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int result=0;
		PreparedStatement ps=DbcpPool.executePreparedStatementReturnPS(conn, sql);
		try
		{
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setString(3, emailAddress);
			ps.setString(4, sid);
			result=ps.executeUpdate();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		needToClose newNeedtoClose=new needToClose();
		newNeedtoClose.setAll(conn, ps, null);
		
		DbcpPool.closeAll(newNeedtoClose);
		return result;
	}

	@Override
	public int update(String sid,String photoPath) {
		// TODO Auto-generated method stub
		String sql="UPDATE student SET photoPath=? WHERE sid=?";
		Connection conn=null;
		try {
			conn=DbcpPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int result=0;
		PreparedStatement ps=DbcpPool.executePreparedStatementReturnPS(conn, sql);
		try
		{
			ps.setString(1, photoPath);
			ps.setString(2, sid);
			result=ps.executeUpdate();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		needToClose newNeedtoClose=new needToClose();
		newNeedtoClose.setAll(conn, ps, null);
		
		DbcpPool.closeAll(newNeedtoClose);
		return result;
	}


}
